/**
 * 
 */
package g.dev.xtable;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author HT
 *
 */
public class UserDao {
	
	public UserDao () {
		try
		{
			Class.forName("org.postgresql.Driver");
		}
		catch (ClassNotFoundException e) {
			// TODO: handle exception
			e.printStackTrace ();
		}
		
	}

	private Connection openConn() {
		Connection conn = null;

		try {
			conn = DriverManager.getConnection (
					"jdbc:postgresql://localhost/quan8", "postgres", "postgres");
		} catch (SQLException e) {
			e.printStackTrace ();
		}

		return conn;
	}

	private void closeConn(Connection conn) {
		try {
			conn.close ();
		} catch (SQLException e) {
			e.printStackTrace ();
		}

	}

	public User findByName(String first, String last) {
		User user = User.empty();

		Connection conn = openConn();
		PreparedStatement select;
		String sql="SELECT id, firstName, lastName, userName from user_test where firstName LIKE ? AND lastName LIKE ?";

		try {
			select=conn.prepareStatement(sql);
			
			select.setString (1, first);
			select.setString (2, last);

			ResultSet result = select.executeQuery ();

			System.out.println ("Got results:");
			while (result.next ()) {// process results one row at a time
				int id = result.getInt(1);
				String firstName = result.getString(2);
				String lastName = result.getString (3);
				String userName = result.getString (4);

				user = new User (id, firstName, lastName, userName);
			}
			select.close ();
			closeConn (conn);
		} catch (SQLException e) {
			e.printStackTrace ();
		}

		return user;
	}

	public List<User> findByMinimalAge (Integer age) {

		Connection conn = openConn ();
		User user = User.empty();

		List<User> list = new ArrayList ();
		try {
			PreparedStatement select = conn.prepareStatement ("SELECT id, firstName, lastName, userName from user_test where age >=?");
			select.setInt (1, age);

			ResultSet result = select.executeQuery();

			System.out.println ("Got results:");
			while (result.next ()) {// process results one row at a time
				int id = result.getInt(1);
				String firstName = result.getString(2);
				String lastName = result.getString (3);
				String userName = result.getString (4);

				user = new User(id, firstName, lastName, userName);
				list.add (user);
			}
			select.close ();
			closeConn(conn);
		} catch (SQLException e) {
			e.printStackTrace ();
		}

		return list;
	}

}
